
clear all
/*insert directory here*/


/*Create PM2.5 emissions for egrid plant data from CEMS*/

use pm25tons, clear
merge 1:1 year plantcode using egridplant
gen pm25mwh=pm25tons/genmwh
sort plantcode year
replace pm25tons=pm25mwh[_n+1]*genmwh if year==1996 & plantcode[_n+1]==plantcode[_n]
drop if statefip==.
replace pm25tons=0 if pm25tons==.
drop _merge pm25mwh ch4tons ch4lbs
save egridplant, replace

use plantemissions, clear
merge m:1 year nerc using nercmwh
keep if year==1996 & _merge==3
collapse mwh (sum) noxtons so2tons co2tons pm25tons, by(year nerc)
merge 1:1 year nerc using test1996
keep if _merge==3

local varlist so2 co2 pm25 nox
foreach i of local varlist{
gen `i'mwh1=`i'tons/mwh
gen `i'comp=`i'tonsmwh/`i'mwh1
}
drop _merge
order so2comp co2comp noxcomp pm25comp, after(nerc)

use plantemissions, clear
keep year statefip ctyfip so2tons noxtons pm25tons co2tons nerc
collapse (sum) so2tons noxtons pm25tons co2tons, by(year statefip ctyfip nerc)
keep if year<1996
save ctyemissions, replace

use egridplant, clear
collapse (sum) pm25tons noxtons so2tons co2tons, by(nerc statefip ctyfip year)
append using ctyemissions
drop if nerc=="OFF-G"
replace nerc="ERCOT" if nerc=="TRE"
replace nerc="WECC" if nerc=="WSCC"
save ctyemissions, replace

/*Collect NERC electricity retail sales (gen and adjust for line losses)*/
use form923, clear
replace statefips=55 if statefips==62
drop if statefips==2 | statefips==15
egen statecheck=max(statefips), by(year plantcode)
gen id=0
replace id=1 if statecheck!=statefips & statefips!=.
egen plantcheck=sum(id), by(plantcode year)
replace statefips=. if plantcheck!=0 & plantcode!=9999 & plantcode!=0
drop plantcheck id statecheck
collapse (sum) gen, by(year plantcode statefips)
merge m:1 plantcode using plant860
drop if statefip==2 | statefip==15
replace statefips=statefip if _merge==3 & statefips==.
drop if _merge==2
drop _merge statefip
replace statefips=42 if plantcode==3202
replace statefips=36 if plantcode==8888
rename statefips statefip
replace ctyfip=0 if ctyfip==.
replace startyear=1900 if startyear==.
keep year plantcode gen statefip ctyfip
collapse (sum) gen, by(year statefip ctyfip)
merge m:1 statefip ctyfip using nerctoelec
drop if _merge==2
replace ctyfip=0 if _merge==1
drop nerc _merge
merge m:1 statefip ctyfip using nerctoelec
drop if _merge==2
drop _merge
collapse (sum) gen, by(year nerc)
replace gen=gen/1.048 if nerc=="ECAR"
replace gen=gen/1.037 if nerc=="ERCOT"
replace gen=gen/.99 if nerc=="FRCC"
replace gen=gen/.945 if nerc=="MAAC"
replace gen=gen/1.0229 if nerc=="MAIN"
replace gen=gen/1.044 if nerc=="MAPP"
replace gen=gen/.993 if nerc=="NPCC"
replace gen=gen/.982 if nerc=="SERC"
replace gen=gen/.933 if nerc=="SPP"
replace gen=gen/.985 if nerc=="WECC"
drop if year>1995
rename gen mwh
append using nercmwh
replace mwh=mwh/1.07
save nercmwh70-11, replace

/*Apply county damages*/
use ctyemissions, clear
replace ctyfip=86 if ctyfip==25 & statefip==12
merge m:1 year statefip ctyfip using damfac
keep if _merge==3
gen pm25damtot=pm25dam*pm25tons
gen so2damtot=so2dam*so2tons
gen co2damtot=co2dam*co2tons
gen noxdamtot=noxdam*noxtons
collapse (sum) pm25damtot so2damtot co2damtot noxdamtot, by(year nerc)
merge 1:1 year nerc using nercmwh70-11
drop _merge
gen dampm25=pm25damtot/mwh
gen damco2=co2damtot/mwh
gen damso2=so2damtot/mwh
gen damnox=noxdamtot/mwh
gen elecdam=dampm25+damco2+damso2+damnox
collapse elecdam, by(year nerc)
reshape wide elecdam, i(year) j(nerc) string
set obs 43
replace year=2001 in 38
replace year=2002 in 39
replace year=2003 in 40
replace year=2006 in 41
replace year=2008 in 42
replace year=2011 in 43
sort year
local varlist ECAR ERCOT FRCC MAAC MAIN MAPP NPCC SERC SPP WECC
forvalues y=2001/2003{
foreach i of local varlist{
replace elecdam`i'=elecdam`i'[_n-1] if year==`y'
}
}

local varlist ERCOT FRCC MRO NPCC RFC SERC SPP WECC
foreach i of local varlist{
replace elecdam`i'=(elecdam`i'[_n-1]+elecdam`i'[_n+1])/2 if year==2006
replace elecdam`i'=(elecdam`i'[_n-1]+elecdam`i'[_n+1])/2 if year==2008
replace elecdam`i'=(elecdam`i'[_n-1]+elecdam`i'[_n+1])/2 if year==2011
}
reshape long elecdam, i(year) j(nerc) string
reshape wide elecdam, i(nerc) j(year)
save elecdam, replace

use nerctoelec, clear
append using nerctoelec04
sort statefip ctyfip nerc
quietly by statefip ctyfip nerc: gen dup=cond(_N==1,0,_n)
drop if dup>1
drop dup
merge m:1 nerc using elecdam
drop _merge
reshape long elecdam, i(statefip ctyfip nerc) j(year)
collapse elecdam, by(statefip ctyfip year)
replace elecdam=elecdam/3.412
label var elecdam "2019 $/mmBtu"
drop if ctyfip==25 & statefip==12
replace ctyfip=102 if ctyfip==113 & statefip==46
save elecdam, replace

/*Create emissions per region*/
use ctyemissions, clear
replace ctyfip=86 if ctyfip==25 & statefip==12
collapse (sum) pm25tons so2tons co2tons noxtons, by(year nerc)
merge 1:1 year nerc using nercmwh70-11
drop if _merge!=3
drop _merge
gen pm25mwh=pm25tons/mwh
gen co2mwh=co2tons/mwh
gen so2mwh=so2tons/mwh
gen noxmwh=noxtons/mwh
drop mwh noxtons co2tons so2tons pm25tons
reshape wide pm25mwh co2mwh so2mwh noxmwh, i(year) j(nerc) string
set obs 43
replace year=2001 in 38
replace year=2002 in 39
replace year=2003 in 40
replace year=2006 in 41
replace year=2008 in 42
replace year=2011 in 43
sort year
local varlist ECAR ERCOT FRCC MAAC MAIN MAPP NPCC SERC SPP WECC
local varlist2 pm25mwh co2mwh so2mwh noxmwh
forvalues y=2001/2003{
foreach i of local varlist{
foreach j of local varlist2{
replace `j'`i'=`j'`i'[_n-1] if year==`y'
}
}
}

local varlist ERCOT FRCC MRO NPCC RFC SERC SPP WECC
local varlist2 pm25mwh co2mwh so2mwh noxmwh
foreach i of local varlist{
foreach j of local varlist2{
replace `j'`i'=(`j'`i'[_n-1]+`j'`i'[_n+1])/2 if year==2006
replace `j'`i'=(`j'`i'[_n-1]+`j'`i'[_n+1])/2 if year==2008
replace `j'`i'=(`j'`i'[_n-1]+`j'`i'[_n+1])/2 if year==2011
}
}

reshape long pm25mwh co2mwh so2mwh noxmwh, i(year) j(nerc) string
reshape wide pm25mwh co2mwh so2mwh noxmwh, i(nerc) j(year)
save elecemissions, replace

use nerctoelec, clear
append using nerctoelec04
sort statefip ctyfip nerc
quietly by statefip ctyfip nerc: gen dup=cond(_N==1,0,_n)
drop if dup>1
drop dup
merge m:1 nerc using elecemissions
drop _merge
reshape long pm25mwh co2mwh so2mwh noxmwh, i(statefip ctyfip nerc) j(year)
collapse pm25mwh co2mwh so2mwh noxmwh, by(statefip ctyfip year)
gen elecpm25=pm25mwh/3.412
gen elecco2=co2mwh/3.412
gen elecso2=so2mwh/3.412
gen elecnox=noxmwh/3.412
drop if ctyfip==25 & statefip==12
drop if ctyfip==0
save elecemissions, replace

